import sqlite
import os
import sys

def openDB(dbname):
        dir = os.listdir(os.getcwd())
        databases = []
        for thing in dir:
                if thing.endswith("db"):
                        databases.append(thing)
        if dbname in databases:
                con = sqlite.connect(dbname, autocommit=1)
        else:
                print "That's not a database! Go make it first!"
                sys.exit()
	return con

def placeItem(cur, room, itemID):
	if recordExists(cur, room, "rooms") and recordExists(cur, itemID, "items"):
		sqlcmd = "UPDATE items SET location = "+str(room)+" WHERE id = "+str(itemID)
		cur.execute(sqlcmd)
	else:
		print "Either your room or your item doesn't exist."
	 

def menu (cur):
	menuoptions = ["Add a room", "List Rooms", "Delete a room", "Connect rooms", "Add item", "List items","Place item"]
	print "What do you want to do?"
	for option in menuoptions:
		print menuoptions.index(option)+1," - ", option
	print "Q - Quit"
	opt = raw_input("Option number: ")
	if opt == "1":
		addRoom(cur)
	elif opt == "2":
		listRooms(cur)
	elif opt == "4":
		linkRooms(cur)
	elif opt == "5":
		addItem(cur)
	elif opt == "6":
		listItems(cur)
	elif opt == "7":
		listRooms(cur)
		room = raw_input("Room ID: ")
		listItems(cur)
		item = raw_input("Item ID: ")
		placeItem(cur, room, item)
	elif opt[0].lower() == "q":
		sys.exit()
	else:
		print "That's not a valid option."

def addItem(cur):
	name = raw_input("Item name: ")
	desc = raw_input("Item description: ")
	
	sqlcmd = "INSERT INTO items (id, name, desc) values (NULL, '"+name+"', '"+desc+"')"
	cur.execute(sqlcmd)

def addRoom(cur):
	roomname = raw_input("Room name: ")
	roomdesc = raw_input("Room Description: ")
	roomname = roomname.replace("'","''")
	roomdesc = roomdesc.replace("'","''")
	sqlcmd = "INSERT INTO rooms (name, desc) VALUES ('"+roomname+"','"+roomdesc+"')"
	cur.execute(sqlcmd)
	print "Room added."
	return
	return

def listRooms(cur):
	rooms = []
	cur.execute('SELECT id, name, desc FROM rooms')
	rooms = cur.fetchall()
	for room in rooms:
		print room[0], "-",room[1], "-", room[2]

def listItems(cur):
	items = []
	cur.execute('SELECT id, name, desc, location FROM items')
	items = cur.fetchall()
	for item in items:
		print item[0], "-",item[1],"-",item[2], " - Room #",item[3]

def recordExists(cur, id, table):
	sqlcmd = "select * from "+table+" where id = "+str(id)
	cur.execute(sqlcmd)
	record = cur.fetchall()
	if record == []:
		return False
	else:
		return True

def dirLinked(cur, room, dir):
	sqlcmd = "SELECT northof, southof, eastof, westof FROM rooms WHERE id = "+str(room)
	cur.execute(sqlcmd)
	dirs = []
	dirs = cur.fetchall()
	dirs = dirs[0]
	if dir[0].lower() == "n":
		return not ((str(dirs[0]) == "0") or (dirs[0] == None))
	elif dir[0].lower() == "s":
		return not ((str(dirs[1]) == "0") or (dirs[1] == None))
	elif dir[0].lower() == "e":
		return not ((str(dirs[2]) == "0") or (dirs[2] == None))
	elif dir[0].lower() == "w":
		return not ((str(dirs[3]) == "0") or (dirs[3] == None))

	else:
		print "That's not a valid direction."

def linkRooms(cur):
	print "Here are all available rooms:"
	listRooms(cur)
	room1 = raw_input("Please enter the number for the first room: ")
	room2 = raw_input("Please enter the number for the second room: ")
	if room1 == room2:
		print "I can't link a room to itself!"
		return	
	elif not recordExists(cur, room1, "rooms"):
		print "Your first choice doesn't exist."
		return
	elif not recordExists(cur, room2, "rooms"):
		print "Your second choice doesn't exist."
		return
	dir = raw_input("How do you get to room two from room one?\nGo [n]orth, [s]outh, [e]ast, or [w]est: ")

	if dir[0] in ["n", "N"]:
		if dirLinked(cur, room1, "s") or dirLinked(cur, room2, "n"):
			print "I can't link those. One is already linked!"
			return
		sqlroom1 = "UPDATE rooms SET southof = "+room2+" where id = "+room1
		sqlroom2 = "UPDATE rooms SET northof = "+room1+" where id = "+room2
	elif dir[0] in ["s", "S"]:
                if dirLinked(cur, room1, "n") or dirLinked(cur, room2, "s"):
                        print "I can't link those. One is already linked!"
                        return
                sqlroom1 = "UPDATE rooms SET northof = "+room2+" where id = "+room1
                sqlroom2 = "UPDATE rooms SET southof = "+room1+" where id = "+room2
	elif dir[0] in ["e", "E"]:
                if dirLinked(cur, room1, "w") or dirLinked(cur, room2, "e"):
                        print "I can't link those. One is already linked!"
                        return
		sqlroom1 = "UPDATE rooms SET westof =  "+room2+"where id = "+room1
		sqlroom2 = "UPDATE rooms SET eastof = "+room1+"where id = "+room2
	elif dir[0] in ["w", "W"]:
                if dirLinked(cur, room1, "e") or dirLinked(cur, room2, "w"):
                        print "I can't link those. One is already linked!"
                        return
		sqlroom1 = "UPDATE rooms SET eastof =  "+room2+"where id = "+room1
		sqlroom2 = "UPDATE rooms SET westof =  "+room1+"where id = "+room2

	cur.execute(sqlroom1)
	cur.execute(sqlroom2)
	

if __name__ == "__main__":
	dbname = raw_input("What database do you want to open? ")
	con = openDB(dbname)
	
	cur = con.cursor()

	while 1 ==1:
		menu(cur)

	con.close()
